package com.teradata.gbase;

import cn.hutool.core.io.FileUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.setting.Setting;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.nio.charset.Charset;
import java.sql.SQLException;
import java.util.List;

/**
 * @Project:
 * @Description:
 * @Version 1.0.0
 * @Throws SystemException:
 * @Author: <li>2019/10/10/010 Administrator Create 1.0
 * @Copyright ©2018-2019 al.github
 * @Modified By:
 */
@Slf4j
public class T2GTool {


    public static void main(String[] args) throws SQLException {
//        Setting setting = new Setting("E:\\workspaces\\hohhot\\bigdata-application\\Teradata2Gbase\\src\\main\\resources\\db.setting");
//        DSFactory ds = DSFactory.create(setting);
//        DataSource dataSource = ds.getDataSource();
//        Db use = new Db(dataSource);
//
//        StringBuffer sb = new StringBuffer();
//        sb.append(getGbaseSql(use, "PDATA", "TB_RES_NWM_CELL_HIS"));
//        sb.append(getGbaseSql(use, "NGBASS", "IOP_EVT_SET_EVT_RELAT"));
//
//        System.out.println(sb.toString());

        System.out.println(getSql("PDATA", "TB_RES_NWM_CELL_HIS"));


    }

    public static void writeGbaseSqlToFile(String configPath, String inputPath, String outputPath) throws SQLException {
        if (FileUtil.exist(inputPath) && !FileUtil.exist(outputPath)) {
            Charset charset = Charset.forName("UTF-8");
            Setting setting = new Setting(configPath);
            DSFactory ds = DSFactory.create(setting);
            DataSource dataSource = ds.getDataSource();
            Db use = new Db(dataSource);
            StringBuffer sb = new StringBuffer();
            List<String> tableNames = FileUtil.readLines(inputPath, charset);
            for (String tableName : tableNames) {
                log.info("正在处理" + tableName + "......");
                String[] args = tableName.split("\\.");
                String sql = getGbaseSql(use, args[0], args[1]);
                log.info("\n" + sql);
                sb.append(sql);
            }
            FileUtil.writeString(sb.toString(), outputPath, charset);
        }
    }

    public static String getGbaseSql(String configPath, String databaseTable) throws SQLException {
        String[] databaseTableName = databaseTable.split("\\.");
        String databaseName = databaseTableName[0];
        String tableName = databaseTableName[1];
        Setting setting = new Setting(configPath);
        DSFactory ds = DSFactory.create(setting);
        DataSource dataSource = ds.getDataSource();
        Db use = new Db(dataSource);
        StringBuffer sb = new StringBuffer();
        sb.append(getGbaseSql(use, databaseName, tableName));
        return sb.toString();
    }

    public static String getGbaseSql(Db use, String dataBaseName, String tableName) throws SQLException {
        StringBuffer sb = new StringBuffer();
        List<Entity> entities = use.query(getSql(dataBaseName, tableName));
        for (Entity entity : entities) {
            sb.append(entity.get("columnname").toString() + "\n");
        }
        return sb.toString();
    }

    public static String getSql(String dataBaseName, String tableName) {
        return "SELECT columnname\n" +
                "FROM (\n" +
                "SELECT T1.columnid,T1.columnname FROM\n" +
                "(\n" +
                "SELECT CAST('0000000' AS VARCHAR(20)) columnid\n" +
                ",CAST('------------------------------------- CREATE TABLE '||UPPER('" + dataBaseName + "')||'.'||UPPER('" + tableName + "')||' ----------------------------' AS VARCHAR(2048)) columnname\n" +
                ") T1\n" +
                "UNION ALL\n" +
                "SELECT T1.columnid,T1.columnname FROM\n" +
                "(\n" +
                "SELECT CAST('0001000' AS VARCHAR(20)) columnid\n" +
                ",CAST('CREATE TABLE IF NOT EXISTS '||UPPER('" + dataBaseName + "')||'.'||UPPER('" + tableName + "')||' (' AS VARCHAR(2048)) columnname\n" +
                ") T1\n" +
                "UNION ALL\n" +
                "SELECT COLUMNID,CASE WHEN (ROW_NUMBER() OVER (PARTITION BY T2.DATABASENAME,T2.TABLENAME ORDER BY COLUMNID))<>1 THEN ','||COLUMNNAME ELSE ' '||COLUMNNAME end COLUMNNAME\n" +
                "FROM\n" +
                "(\n" +
                "SELECT       CAST(((COLUMNID) (FORMAT 'Z9999')) AS VARCHAR(10))||'000' AS COLUMNID,\n" +
                "\t     UPPER(TRIM(T1.COLUMNNAME))\n" +
                "\t     ||' '||CASE WHEN T1.COLUMNTYPE = 'BF' THEN\n" +
                "\t    'TINYINT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'I1' THEN\n" +
                "\t    'SMALLINT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'VF' THEN\n" +
                "\t    'BINARY'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'DA' THEN\n" +
                "\t    'DATE'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'F' THEN\n" +
                "\t    'FLOAT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'I2' THEN\n" +
                "\t    'SMALLINT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'I' THEN\n" +
                "\t    'INT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'CF' THEN\n" +
                "\t    'VARCHAR('||TRIM(CAST(((T1.COLUMNLENGTH) (FORMAT '-(10)9')) AS VARCHAR(10)))||')'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'CV' THEN\n" +
                "\t    'VARCHAR('||TRIM(CAST(((T1.COLUMNLENGTH) (FORMAT '-(10)9')) AS VARCHAR(10)))||')'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'D' THEN\n" +
                "\t    'DECIMAL' || '(' ||TRIM(T1.DECIMALTOTALDIGITS) || ',' || TRIM(T1.DECIMALFRACTIONALDIGITS) || ')'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'I8' THEN\n" +
                "\t     'BIGINT'\n" +
                "\t    WHEN T1.COLUMNTYPE = 'TS' THEN\n" +
                "\t     'TIMESTAMP'\n" +
                "      ELSE\n" +
                "\t     'UNKNOW'\n" +
                "      END\n" +
                "     ||' '||'COMMENT '||''''||COALESCE(TRIM(T1.COLUMNTITLE),'')||''''\n" +
                "     ||' '||CASE WHEN T2.COLUMNNAME IS NULL THEN '' ELSE '-- Y' END  COLUMNNAME\n" +
                "     ,T1.DATABASENAME\n" +
                "     ,T1.TABLENAME\n" +
                "    FROM DBC.COLUMNS T1\n" +
                "    LEFT JOIN DBC.Indices T2\n" +
                "    ON T1.DATABASENAME = T2.DATABASENAME\n" +
                "    AND T1.TABLENAME = T2.TABLENAME\n" +
                "    AND T1.COLUMNNAME = T2.COLUMNNAME\n" +
                "    AND (T2.IndexType = 'Q' OR T2.IndexType = 'P')\n" +
                "    WHERE T1.DATABASENAME='" + dataBaseName + "'\n" +
                "    AND T1.TABLENAME='" + tableName + "'\n" +
                ") T2\n" +
                "UNION ALL\n" +
                "SELECT T1.columnid,T1.columnname\n" +
                "FROM\n" +
                "(\n" +
                "SELECT CAST('9995000' AS VARCHAR(20)) columnid\n" +
                ",CAST(') DISTRIBUTED BY ('''||TRIM(t1.columnname)||''')' AS VARCHAR(1024)) AS columnname\n" +
                "FROM DBC.Indices T1\n" +
                "INNER JOIN DBC.COLUMNS T3\n" +
                "ON T1.DATABASENAME=T3.DATABASENAME\n" +
                "AND T1.TABLENAME=T3.TABLENAME\n" +
                "AND T1.COLUMNNAME=T3.COLUMNNAME\n" +
                "AND T3.COLUMNTYPE NOT IN ('DA')\n" +
                "WHERE (T1.IndexType = 'Q' OR T1.IndexType = 'P')\n" +
                "AND t1.databasename='" + dataBaseName + "'\n" +
                "AND t1.TABLENAME='" + tableName + "'\n" +
                "QUALIFY ROW_NUMBER() OVER (PARTITION BY t1.databasename,t1.TABLENAME ORDER BY t1.accesscount DESC)=1\n" +
                ") T1\n" +
                "UNION ALL\n" +
                "SELECT T1.columnid,T1.columnname\n" +
                "FROM (\n" +
                "SELECT '9997000' columnid,'COMMENT '''||COALESCE(TRIM(t1.CommentString),'')||''';'   columnname\n" +
                "FROM   dbc.tablesv T1\n" +
                "WHERE T1.DATABASENAME='" + dataBaseName + "'\n" +
                "AND T1.TABLENAME='" + tableName + "'\n" +
                ") T1\n" +
                ") T3\n" +
                "ORDER BY columnid";
    }
}
